package com.cms.biz.business.impl;

import java.sql.ResultSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cms.biz.business.MaintenanceDeliveryNoticeServiceMgr;
import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.dict.DMTServiceMgr;
import com.efounder.dict.FCTServiceMgr;
import com.efounder.eai.data.JParamObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.framework.sys.business.AbstractBusinessObjectServiceMgr;
import com.metadata.biz.MetaDataServiceMgr;

@Transactional(rollbackFor = Exception.class)
@Service("MaintenanceDeliveryNoticeServiceMgr")
public class MaintenanceDeliveryNoticeMgrImpl extends AbstractBusinessObjectServiceMgr implements MaintenanceDeliveryNoticeServiceMgr {
	
	@Autowired
	private JdbcTemplate                    jdbcTemplate;
	
	@Autowired
	private MetaDataServiceMgr         metaDataServiceMgr;

	@Autowired
	private DMTServiceMgr                      DMTService;
	
	@Autowired
	private FCTServiceMgr                      FCTService;
	
	public EFRowSet loadNewWXCCTZForm(JParamObject PO) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance();
		EFRowSet   headRowset = EFRowSet.getInstance();
		String         F_HTBH = PO.GetValueByParamName("F_HTBH", "");
		String         F_XMBH = PO.GetValueByParamName("F_XMBH", "");
		String         F_KJQJ = PO.GetValueByParamName("F_KJQJ", "");
		String         F_GUID = PO.GetValueByParamName("F_GUID", "");
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			//形成头信息
			strSql = "select '" + F_KJQJ + "' as F_KJQJ, '" + F_GUID + "' as F_GUID, WXTZXX.F_HTBH,max(HTXXZD.F_HTMC) as F_HTBHMC,WXTZXX.F_XMBH,"
				   + " max(HTXMZD.F_XMMC) as F_XMBHMC, max(WXTZXX.F_JSQD) as F_JSQD,max(WXTZXX.F_SFCQ) as F_SFCQ, max(WXTZXX.F_JSFZR) as F_JSFZR, max(HYZGZD.F_ZGMC) AS F_JSFZRMC, "
				   + " max(WXTZXX.F_HTQK) as F_HTQK, max(WXTZXX.F_JHQK) as F_JHQK,max(WXTZXX.F_ZJLY) as F_ZJLY from WXTZXX"
				   + " LEFT JOIN HTXMZD ON WXTZXX.F_XMBH = HTXMZD.F_XMBH"
				   + " LEFT JOIN HTXXZD ON WXTZXX.F_HTBH = HTXXZD.F_HTBH"
				   + " LEFT JOIN HYZGZD ON WXTZXX.F_JSFZR = HYZGZD.F_ZGBH"
				   + " where WXTZXX.F_HTBH = '" + F_HTBH + "' and WXTZXX.F_XMBH = '" + F_XMBH + "' and WXTZXX.F_DJZT = '1' "
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'WXTZ_FLOW' " 
				   + " and FLOW_TASK_END_LIST.BIZ_GUID = WXTZXX.F_GUID)"
				   + " group by WXTZXX.F_HTBH,WXTZXX.F_XMBH";
			rs = stmt.executeQuery(strSql);
			if(rs != null && rs.next()) DataSetUtils.resultSet2RowSet(rs, headRowset);
			
			//形成明细信息
			strSql = "select '" + F_KJQJ + "' as F_KJQJ, '" + F_GUID + "' as F_GUID, WXTZMX.F_XMBH,max(HTXMZD.F_XMMC) as F_XMBHMC," 
			       + " WXTZMX.F_CPBH,max(HTCPZD.F_CPMC) as F_CPBHMC, max(HTCPZD.F_GGXH) as F_GGXH, max(HTCPZD.F_JLDW) as F_JLDW,"
				   + " ifnull(round(sum(WXTZMX.F_SCSL), 2), 0) as F_SCSL, "
				   + " ifnull(round(sum(WXSYSL_V.F_CCSL), 2), 0) as F_CCSL , 0 as F_SYSL "
				   + " from WXTZMX"
				   + " LEFT JOIN HTXMZD ON WXTZMX.F_XMBH = HTXMZD.F_XMBH"
				   + " LEFT JOIN HTCPZD ON WXTZMX.F_CPBH = HTCPZD.F_CPBH"
				   + " LEFT JOIN WXSYSL_V ON WXTZMX.F_XMBH = WXSYSL_V.F_XMBH and WXTZMX.F_CPBH = WXSYSL_V.F_CPBH"
				   + " where exists(select 1 from WXTZXX where WXTZXX.F_DJBH = WXTZMX.F_DJBH and WXTZXX.F_KJQJ = WXTZMX.F_KJQJ and " 
				   + " WXTZXX.F_GUID = WXTZMX.F_GUID  and WXTZXX.F_HTBH = '" + F_HTBH + "' and WXTZXX.F_XMBH = '" + F_XMBH + "' and "
				   + " WXTZXX.F_DJZT = '1')"
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'WXTZ_FLOW' and " 
				   + " FLOW_TASK_END_LIST.BIZ_GUID = WXTZMX.F_GUID)"
				   + " group by WXTZMX.F_XMBH,WXTZMX.F_CPBH"
				   + " order by WXTZMX.F_XMBH,WXTZMX.F_CPBH";
			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
			headRowset.setDataSet("WXCCTZMX", dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return headRowset;
	}

	@Override
	public EFRowSet loadEditWXCCTZForm(JParamObject PO) {
		JConnection      conn = null;
		JStatement       stmt = null;
		ResultSet          rs = null;
		String         strSql = "";
		EFDataSet     dataset = EFDataSet.getInstance();
		EFDataSet   wxdataset = EFDataSet.getInstance();
		EFRowSet   headRowset = EFRowSet.getInstance();
		EFRowSet     wxRowset = EFRowSet.getInstance();
		String         F_GUID = PO.GetValueByParamName("F_GUID", "");
		double           scsl = 0.0;
		double           ccsl = 0.0;
		double           sysl = 0.0;
		String[]     primeKey = null;
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();

			//形成头信息
			strSql = "select WXCCTZXX.F_CHDATE as F_CHDATE,WXCCTZXX.F_CRDATE as F_CRDATE,WXCCTZXX.F_DJBH as F_DJBH,WXCCTZXX.F_DJZT as F_DJZT," 
				   + "WXCCTZXX.F_GUID as F_GUID,WXCCTZXX.F_HTBH as F_HTBH,F_HTBH.F_HTMC as F_HTBHMC,WXCCTZXX.F_JSFZR as F_JSFZR," 
				   + "F_JSFZR.F_ZGMC as F_JSFZRMC,WXCCTZXX.F_JSQD as F_JSQD,WXCCTZXX.F_KJQJ as F_KJQJ,WXCCTZXX.F_NOTE as F_NOTE," 
				   + "WXCCTZXX.F_SFCQ as F_SFCQ,WXCCTZXX.F_WGRQ as F_WGRQ,WXCCTZXX.F_XMBH as F_XMBH,F_XMBH.F_XMMC as F_XMBHMC," 
				   + "WXCCTZXX.F_ZDR as F_ZDR,F_ZDR.USER_NAME as F_ZDRMC,WXCCTZXX.F_ZDRQ as F_ZDRQ,WXCCTZXX.F_HTQK as F_HTQK,WXCCTZXX.F_JHQK as F_JHQK,WXCCTZXX.F_ZJLY as F_ZJLY" 
				   + " from WXCCTZXX WXCCTZXX " 
				   + "LEFT JOIN HTXXZD F_HTBH ON F_HTBH.F_HTBH = WXCCTZXX.F_HTBH " 
				   + "LEFT JOIN HYZGZD F_JSFZR ON F_JSFZR.F_ZGBH = WXCCTZXX.F_JSFZR " 
				   + "LEFT JOIN HTXMZD F_XMBH ON F_XMBH.F_XMBH = WXCCTZXX.F_XMBH " 
				   + "LEFT JOIN BSUSER F_ZDR ON F_ZDR.USER_ID = WXCCTZXX.F_ZDR " 
				   + "where  WXCCTZXX.F_GUID = '" + F_GUID + "' " 
				   + "order by WXCCTZXX.F_DJBH";
			rs = stmt.executeQuery(strSql);
			if(rs != null && rs.next()) DataSetUtils.resultSet2RowSet(rs, headRowset);
			
			//形成出厂通知单明细信息
			strSql = " select WXCCTZMX.F_CHDATE as F_CHDATE,WXCCTZMX.F_CPBH as F_CPBH,F_CPBH.F_CPMC as F_CPBHMC,WXCCTZMX.F_CRDATE as F_CRDATE," 
				   + " WXCCTZMX.F_DJBH as F_DJBH,WXCCTZMX.F_FLBH as F_FLBH,WXCCTZMX.F_GUID as F_GUID," 
				   + " WXCCTZMX.F_KJQJ as F_KJQJ,WXCCTZMX.F_CCSL as F_CCSL,WXCCTZMX.F_XMBH as F_XMBH,F_XMBH.F_XMMC as F_XMBHMC, " 
				   + " F_CPBH.F_GGXH as F_GGXH, F_CPBH.F_JLDW as F_JLDW, WXSYSL_V.F_CCSL as F_YCCSL" 
				   + " from WXCCTZMX WXCCTZMX " 
				   + " LEFT JOIN HTCPZD F_CPBH ON F_CPBH.F_CPBH = WXCCTZMX.F_CPBH " 
				   + " LEFT JOIN HTXMZD F_XMBH ON F_XMBH.F_XMBH = WXCCTZMX.F_XMBH " 
				   + " LEFT JOIN WXSYSL_V ON WXCCTZMX.F_XMBH = WXSYSL_V.F_XMBH and WXCCTZMX.F_CPBH = WXSYSL_V.F_CPBH"
				   + " where  WXCCTZMX.F_GUID = '" + F_GUID + "' " 
				   + " order by F_DJBH,WXCCTZMX.F_FLBH";

			rs = stmt.executeQuery(strSql);
			dataset = DataSetUtils.resultSet2DataSet(rs, dataset);
			
			
			//形成生产通知单明细信息
			strSql = "select WXTZMX.F_XMBH,WXTZMX.F_CPBH, round(sum(WXTZMX.F_SCSL), 2) as F_SCSL "
				   + " from WXTZMX"
				   + " where exists(select 1 from WXTZXX where WXTZXX.F_DJBH = WXTZMX.F_DJBH and WXTZXX.F_KJQJ = WXTZMX.F_KJQJ and " 
				   + " WXTZXX.F_GUID = WXTZMX.F_GUID  and WXTZXX.F_HTBH = '" + headRowset.getString("F_HTBH", "") + "' and WXTZXX.F_XMBH = '" 
				   + headRowset.getString("F_XMBH", "") + "' and "
				   + " WXTZXX.F_DJZT = '1')"
				   + " and exists(select 1 from FLOW_TASK_END_LIST where FLOW_TASK_END_LIST.FLOW_ID = 'WXTZ_FLOW' and " 
				   + " FLOW_TASK_END_LIST.BIZ_GUID = WXTZMX.F_GUID)"
				   + " group by WXTZMX.F_XMBH,WXTZMX.F_CPBH"
				   + " order by WXTZMX.F_XMBH,WXTZMX.F_CPBH";
			rs = stmt.executeQuery(strSql);
			wxdataset = DataSetUtils.resultSet2DataSet(rs, wxdataset);
			wxdataset.setPrimeKey(new String[]{"F_XMBH", "F_CPBH"});
			wxdataset.buildPrimeKeyIndex();
			
			for(int i = 0; i < dataset.getRowCount(); i++) {
				primeKey = new String[]{dataset.getRowSet(i).getString("F_XMBH", ""), dataset.getRowSet(i).getString("F_CPBH", "")};
				wxRowset = (EFRowSet) wxdataset.getRowSet(primeKey);
				
				if(wxRowset != null) {
					scsl = wxRowset.getNumber("F_SCSL", 0.0).doubleValue();
					dataset.getRowSet(i).putString("F_SCSL", String.valueOf(wxRowset.getNumber("F_SCSL", 0.0)));
				}
				ccsl = dataset.getRowSet(i).getNumber("F_YCCSL", 0.0).doubleValue();
				sysl = scsl - ccsl;
				dataset.getRowSet(i).putString("F_SYSL", String.valueOf(sysl));
			}
			
			headRowset.setDataSet("WXCCTZMX", dataset);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);	
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return headRowset;
	}

}
